package com.github.m6d21.sso.common;

import com.github.m6d21.sso.business.controller.test.model.Car;
import com.github.m6d21.sso.common.model.ExcelQaSchemaInfo;
import com.github.m6d21.sso.common.util.excel.ExcelHelper;
import java.io.Closeable;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;

/**
 *<p>
 *
 *</p>
 *
 * @author liuyang
 * @date 2019/1/10 Thu 17:43:00
 * @since 1.0.0
 */
public class GenerateQaBlacklistSql {

    private final String tableName = "qa_anew_car_filter_blacklist";

    private String insertTemplate = "INSERT IGNORE INTO `qa_anew_car_filter_blacklist`(`brand_code`,`brand_name`,`series_code`,`series_name`,`model_code`,`model_name`,`business`)VALUE('${brand_code}','${brand_name}','${series_code}','${series_name}','${model_code}','${model_name}','JIA_XUAN');";

    @Test
    public void testGenerateUpdateSql() {
        String excelUrl = "file:///Users/liuyang/Downloads/car-brand-series-model.xlsx";
        InputStream isp = null;
        try {
            URL url = new URL(excelUrl);
            isp = url.openConnection().getInputStream();
            ExcelHelper<Car> helper = ExcelHelper.newInstance(Car.class);
            List<Car> carList = helper.parseExcel(isp);
            System.out.println("共有" + carList.size() + "条excel数据");
            List<String> sqlList = generateUpdateSql(carList);
            System.out.println("生成" + sqlList.size() + "条SQL");
            writeToFile(sqlList);
        } catch (IOException e) {
            System.out.println("读取文件失败");
        } finally {
            close(isp);
        }

    }

    private List<String> generateUpdateSql(List<Car> carList) {
        if (CollectionUtils.isEmpty(carList)) {
            return new ArrayList<>(0);
        }

        List<String> sqlList = new ArrayList<>(1028);
        for (Car car : carList) {
            if (car == null) {
                continue;
            }
            String brandCode = Optional.ofNullable(car).map(Car::getBrandCode).orElse("");
            String brandName = Optional.ofNullable(car).map(Car::getBrandName).orElse("");
            String seriesCode = Optional.ofNullable(car).map(Car::getSeriesCode).orElse("");
            String seriesName = Optional.ofNullable(car).map(Car::getSeriesName).orElse("");
            String modelCode = Optional.ofNullable(car).map(Car::getModelCode).orElse("");
            String modelName = Optional.ofNullable(car).map(Car::getModelName).orElse("");
            String sql = insertTemplate.replaceAll("\\$\\{brand_code}", brandCode);
            sql = sql.replaceAll("\\$\\{brand_name}", brandName);
            sql = sql.replaceAll("\\$\\{series_code}", seriesCode);
            sql = sql.replaceAll("\\$\\{series_name}", seriesName);
            sql = sql.replaceAll("\\$\\{model_code}", modelCode);
            sql = sql.replaceAll("\\$\\{model_name}", modelName);
            sqlList.add(sql);
        }

        return sqlList;
    }

    private String wrapField(String s) {
        if (StringUtils.isBlank(s)) {
            return null;
        }
        return " `" + s + "` ";
    }

    private String wrapValue(String s) {
        if (StringUtils.isBlank(s)) {
            return null;
        }
        return " '" + s + "' ";
    }

    private String appendCondition(String filed, String value) {
        if (StringUtils.isBlank(value) || StringUtils.isBlank(filed)) {
            return "";
        }
        return " and " + wrapField(filed) + " = " + wrapValue(value);
    }

    private void writeToFile(List<String> strings) {
        if (CollectionUtils.isEmpty(strings)) {
            return;
        }
        String fileName = tableName;

        FileWriter fw = null;
        PrintWriter pw = null;
        int count = 0;
        File file = null;
        for (String s : strings) {
            try {
                if (count % 600 == 0) {
                    file = createFile(fileName + (count / 600) + ".sql");
                }
                count++;
                fw = new FileWriter(file, true);
                pw = new PrintWriter(fw);
//                if(count++%600==0){
//                    pw.println("#----"+count);
//                }
                pw.println(s);
            } catch (IOException e) {
                System.out.println("写入文件失败");
            } finally {
                close(fw);
                close(pw);
            }
        }
    }

    private void close(Closeable x) {
        if (x != null) {
            try {
                x.close();
            } catch (IOException e) {
                System.out.println("关闭失败");
            }
        }
    }

    private boolean isNumber(String s) {
        String regexp = "^[\\d]+[\\.]{0,1}[\\d]{0,}$";
        if (StringUtils.isBlank(s)) {
            return false;
        }
        Pattern pattern = Pattern.compile(regexp);
        Matcher matcher = pattern.matcher(s);
        return matcher.matches();
    }

    private File createFile(String fileName) {
        if (StringUtils.isEmpty(fileName)) {
            return null;
        }
        File file = new File(fileName);

        if (!file.exists()) {
            try {
                boolean b = file.createNewFile();
                if (!b) {
                    System.out.println("创建文件失败");
                }
            } catch (IOException e) {
                System.out.println("创建文件失败");
            }
        }

        return file;
    }

    @Test
    public void test() {
        System.out.println(isNumber("5.33"));
    }

    public List<ExcelQaSchemaInfo> readExcel(String fileUrl) {
        InputStream isp = null;
        List<ExcelQaSchemaInfo> list = null;
        try {
            URL url = new URL(fileUrl);
            isp = url.openConnection().getInputStream();
            ExcelHelper<ExcelQaSchemaInfo> helper = ExcelHelper.newInstance(ExcelQaSchemaInfo.class);
            list = helper.parseExcel(isp);
            System.out.println("共有" + list.size() + "条excel数据");
        } catch (IOException e) {
            System.out.println("读取文件失败");
        } finally {
            close(isp);
        }

        return Optional.of(list).orElse(new ArrayList<>(0));
    }

    @Test
    public void checkExcelData() {
        String excelUrl1 = "file:///Users/liuyang/Downloads/qa_scheme_info.xlsx";
        String excelUrl2 = "file:///Users/liuyang/project/qa_sheme_info_bak.xlsx";

        List<ExcelQaSchemaInfo> list1 = readExcel(excelUrl1);
        List<ExcelQaSchemaInfo> list2 = readExcel(excelUrl2);
        Map<String, ExcelQaSchemaInfo> map1 = new HashMap<>();
        String splitter = "-";
        list1.forEach(info -> map1.put(info.getId() + splitter + info.getBasicPrice(), info));
        list2.forEach(excelQaSchemaInfo -> {
            ExcelQaSchemaInfo info = map1.get(excelQaSchemaInfo.getId() + splitter + excelQaSchemaInfo.getBasicPrice());
            if (info == null) {
                System.out.println(excelQaSchemaInfo.getId() + ": 数据紊乱！");
            }
        });

    }


    @Test
    public void testReplace() {
        String sql = insertTemplate.replaceAll("\\$\\{brand_code}", "1111");
        System.out.println(sql);
    }

}
